/*
 * 
 * Version 1.00
 * 
 * Created on 2007-6-4
 * Encoding GB2312
 */
package net.comm.user.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

import net.comm.page.PageCtr;
import net.comm.param.service.impl.SequenceMgr;
import net.comm.util.DatetimeUtil;
import net.comm.check.service.impl.JifenServiceImpl;
import net.comm.common.GlbFinal;
import net.comm.common.ReturnMessage;
import net.comm.common.common;
import net.comm.file.service.impl.FilesServiceImpl;
import net.comm.file.web.form.FilesForm;
import net.comm.group.db.DbGMemberService;
import net.comm.group.db.GMemberServiceSqlMysql;

import net.comm.user.bean.BaseDetails;
import net.comm.user.bean.BaseUserHome;
import net.comm.user.db.DbUserHomeService;
import net.comm.user.db.UserHomeServiceSqlMysql;
import net.comm.user.web.form.UserHomeForm;
import net.comm.user.web.form.UsersForm;
import net.comm.util.DbcpPool;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.sakaiproject.db.impl.BasicSqlService;


/**
 *
 * @author hcgong(hcgong@sina.com)
 * @version 1.0
 * @date 2007-6-4
 */
public class UserHomeServiceImpl {

	static Logger logger = Logger.getLogger(UserHomeServiceImpl.class.getName());

	/**
	 * 查询最近日期加入网站的小区人员
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List listbyCtime(UserHomeForm theForm,  ReturnMessage msg) 
	{
		List list = null;

		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			UserHomeServiceSqlMysql servSql = new UserHomeServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();

			Object[] fields2 = new Object[2];
			
			fields2[0] = theForm.getQryVillageID();
			fields2[1] = theForm.getQryCtime();

			/*
			 * 获得查询SQL语句
			 */
			String sql = servSql.getCountSql3();

			/**
			 * 根据SQL执行结果计算分页相关信息
			 */
			PageCtr page = theForm.getPage();
			page.setTotalNum(bSqlServ.dbCount(cn, sql, fields2));
			page.caculate();

			//取得FORM中传入的查询参数
			Object[] fields = new Object[4];
			
			fields[0] = theForm.getQryVillageID();
			fields[1] = theForm.getQryCtime();
			fields[2] = page.getPos()-1;
			fields[3] = page.getPageSize();
			
			sql = servSql.getRecbyCtimeSql();

			list = bSqlServ.dbRead(cn, sql, fields, new DbUserHomeService());
			
		    DbcpPool.returnConnection(cn);
		    
		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
			
		} finally {
			DbcpPool.returnConnection(cn);
		}		
		
		return list;
	}
	/**
	 * 根据用户名搜索会员
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List listbyNick(UserHomeForm theForm,  ReturnMessage msg) 
	{
		List list = null;

		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			UserHomeServiceSqlMysql servSql = new UserHomeServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();

			Object[] fields2 = new Object[1];
			
			fields2[0] = "%"+theForm.getQryNick() + "%";

			/*
			 * 获得查询SQL语句
			 */
			String sql = servSql.getCountSql4();

			/**
			 * 根据SQL执行结果计算分页相关信息
			 */
			PageCtr page = theForm.getPage();
			page.setTotalNum(bSqlServ.dbCount(cn, sql, fields2));
			page.caculate();

			//取得FORM中传入的查询参数
			Object[] fields = new Object[3];
			
			fields[0] = "%"+theForm.getQryNick() + "%";
			fields[1] = page.getPos()-1;
			fields[2] = page.getPageSize();
			
			sql = servSql.getRecbyNickSql();

			list = bSqlServ.dbRead(cn, sql, fields, new DbUserHomeService());
			
		    DbcpPool.returnConnection(cn);
		    
		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
			
		} finally {
			DbcpPool.returnConnection(cn);
		}		
		
		return list;
	}	
	/**
	 * 返回根据会员ID查询的会员操作日志
	 */
	public UserHomeForm list(int  userID) 
	{
		Connection cn = null;
		UserHomeForm theForm = new UserHomeForm();
		try
		{
			cn = DbcpPool.getConnection();
			
			//取得FORM中传入的查询参数
			Object[] fields = new Object[1];
			
			fields[0] = userID;			

			UserHomeServiceSqlMysql servSql = new UserHomeServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();

			/*
			 * 获得查询SQL语句
			 */
			String sql = servSql.getByUserIDSql();
									
			List list = bSqlServ.dbRead(cn, sql, fields, new DbUserHomeService());
									
			if(list.size() > 0){
				BaseUserHome base = (BaseUserHome)list.get(0);
				BeanUtils.copyProperties(theForm, base);
			}
			
		    DbcpPool.returnConnection(cn);
		    
		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return theForm;
	}
	
	/**
	 * 
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List adlist(UserHomeForm theForm,  ReturnMessage msg) 
	{
		List list = new Vector();

		Connection cn = null;
		Statement stmt=null;
		ResultSet rs=null;
		try
		{
			cn = DbcpPool.getConnection();
			
			UserHomeServiceSqlMysql servSql = new UserHomeServiceSqlMysql();
			
			StringBuffer sb = new StringBuffer(" where 1=1");

			String qrysts = theForm.getQryFcPicIDSts().replaceAll(GlbFinal.SQLINjection, " ");
			if(!qrysts.equals("")){
				
				sb.append(" and FcPicIDSts = ");
				sb.append(qrysts);
				sb.append(" ");
			}
			if(theForm.getQryVillageID() > 0){
				
				sb.append(" and VillageID = ");
				sb.append(theForm.getQryVillageID());
				sb.append(" ");
			}
			String qryOrderby = theForm.getQryOrderby().replaceAll(GlbFinal.SQLINjection, " ");
			if(!qryOrderby.equals("")){
				
				sb.append(" Order by ");
				sb.append(qryOrderby);
				sb.append(" ");
			}else{
				sb.append(" Order by Mtime desc ");
			}
			
			
			//查询记录数
			stmt = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			String sql = servSql.getCountSql1();

			logger.debug(sql+sb.toString());
			rs=stmt.executeQuery(sql+sb.toString());
			int nRows = 0;
			if (rs.next())
	           nRows = rs.getInt("COUNT");
			else
			{
	            rs.close();
	            stmt.close();
	            DbcpPool.returnConnection(cn);
			    return list;
			}
			/**
			 * 根据SQL执行结果计算分页相关信息
			 */
			PageCtr page = theForm.getPage();
			page.setTotalNum(nRows);
			page.caculate();

			sb.append(" limit ");
			sb.append(page.getPos()-1);
			sb.append(", ");
			sb.append(page.getPageSize());
			
			 //取记录
			 sql = servSql.getRecSql();
			 logger.debug(sql+sb.toString());
			 
			 DbUserHomeService dbservice = new DbUserHomeService();
			 
			 rs=stmt.executeQuery(sql+sb.toString());
			 while (rs.next())
			 {
				try
				{
					Object obj = dbservice.readSqlResultRecord(rs);
					if (obj != null) list.add(obj);
				}
				catch (Throwable t)
				{
					logger.error(t.getMessage());
				}		
			 }
			 rs.close();
			 stmt.close();
		     DbcpPool.returnConnection(cn);

		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				DbcpPool.returnConnection(cn);
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}			
	    }finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}			
			DbcpPool.returnConnection(cn);
		}		
		return list;
	}	
	/**
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#get(net.swift.db.tab_paramnostro, net.swift.common.ReturnMessage)
	 */
	public void get(UserHomeForm theForm, ReturnMessage msg) 
	{

		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			//取得FORM中传入的查询参数
			Object[] fields = new Object[1];
			
			fields[0] = theForm.getSeqID();
						
			UserHomeServiceSqlMysql servSql = new UserHomeServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			
			String sql = servSql.getByUserIDSql();
						
			List list = bSqlServ.dbRead(cn, sql, fields, new DbUserHomeService());
			if(list.size() > 0){
				BaseUserHome base = (BaseUserHome)list.get(0);
				BeanUtils.copyProperties(theForm, base);				
			}
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);			
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}	
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}
	/**
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#get(net.swift.db.tab_paramnostro, net.swift.common.ReturnMessage)
	 */
	public void getbyuserID(UserHomeForm theForm, ReturnMessage msg) 
	{

		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			//取得FORM中传入的查询参数
			Object[] fields = new Object[1];
			
			fields[0] = theForm.getUserID();
						
			UserHomeServiceSqlMysql servSql = new UserHomeServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			
			String sql = servSql.getByUserIDSql();
						
			List list = bSqlServ.dbRead(cn, sql, fields, new DbUserHomeService());
			if(list.size() > 0){
				BaseUserHome base = (BaseUserHome)list.get(0);
				BeanUtils.copyProperties(theForm, base);				
			}
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);			
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}	
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}	
	/**
	 * 添加到表中
	 * @see net.IUserbasicService.module.other.IUserService#add(net.swift.common.ReturnMessage)
	 */
	public void add(UserHomeForm theForm, ReturnMessage msg) 
	{
		
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			DbcpPool.beginTran(cn);
			
			add(cn, theForm, msg);
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);
			DbcpPool.returnConnection(cn);

		} catch(Exception ex) 
		{
			common.setAppFailMsg(msg , ex.getMessage());
			logger.error("System error , param get about info" + ex);
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}
	/**
	 * 添加到表中
	 * @see net.IUserbasicService.module.other.IUserService#add(net.swift.common.ReturnMessage)
	 */
	public void add(Connection cn, UserHomeForm theForm, ReturnMessage msg) 
	{
		
		try
		{
			
			UserHomeServiceSqlMysql servSql = new UserHomeServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			
			theForm.setSeqID(SequenceMgr.generateSeq( GlbFinal.SEQ_INITDATE,GlbFinal.SEQ_USERHOMEID));
			
			//保存房产证照片
			if(theForm.getFcPicIDPath() != null&&!theForm.getFcPicIDPath().getFileName().equals("")){
				
				FilesForm fileForm = new FilesForm();
				fileForm.setFormFile(theForm.getFcPicIDPath());
				fileForm.setF_info(theForm.getFangchanNo());
				fileForm.setF_Zip(GlbFinal.ZIP_200_200);
				
				FilesServiceImpl fileservice = new FilesServiceImpl();
				
				fileservice.add(cn, fileForm, msg);
				if(msg.getErrCode() != 0){
					return;
				}
				theForm.setFcPicID(fileForm.getF_ID());
			}				
			
			//取得FORM中传入的查询参数
			Object[] fields = new Object[19];
			
			fields[0] = theForm.getSeqID();			//	序号ID
			fields[1] = theForm.getUserID();			//	会员ID
			fields[2] = theForm.getWork_location();	//	所在省份
			fields[3] = theForm.getWork_sublocation();	//	所在城市
			fields[4] = theForm.getWork_area();			//	所在区县
			fields[5] = theForm.getVillageID();			//	小区
			fields[6] = theForm.getBuilding();			//	栋
			fields[7] = theForm.getHouseNo();			//	号
			fields[8] = theForm.getFangchanNo();			//	房产证号码
			fields[9] = theForm.getFcNoSet();			//	房产证号码隐私保护
			fields[10] = theForm.getFcPicID();			//	房产证照片ID
			fields[11] = theForm.getFcPicIDSts();			//	房产证照片状态
			fields[12] = theForm.getInTime();			//	入住时间
			fields[13] = theForm.getInTimeSet();			//	入住时间隐私保护
			fields[14] = theForm.getFamSts();			//	家庭状况
			fields[15] = theForm.getFamStsSet();			//	家庭状况隐私保护
			fields[16] = theForm.getLiveSts();		//	居住状况
			fields[17] = theForm.getLiveStsSet();		//	居住状况隐私保护
			fields[18] = DatetimeUtil.getDatetime();
			
			String sql = servSql.getInsertSql();
						
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 添加居住信息表失败！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return ;
			}
		
		} catch(Exception ex) 
		{
			common.setAppFailMsg(msg , ex.getMessage());
			logger.error("System error , param get about info" + ex);
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		}
		
		return ;
	}	
	/**
	 * 删除一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void delete(UserHomeForm theForm, ReturnMessage msg) 
	{
		
		return ;
	}
	/**
	 * 更新一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void updsts(UserHomeForm theForm, ReturnMessage msg) 
	{
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			DbcpPool.beginTran(cn);

			//取原记录的信息			
			Object[] fields2 = new Object[1];

			fields2[0] = theForm.getSeqID();
			
			List list = null;
			
			UserHomeServiceSqlMysql servSql = new UserHomeServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			
			String sql = servSql.getbykeySql();
		
			list = bSqlServ.dbRead(cn, sql, fields2, new DbUserHomeService());

			//记录不存在 新增记录
			if(list.size() == 0){
				common.setAppFailMsg(msg , " 取居住信息失败！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return ;			
			}
			//填充到theForm的空字段中
			BaseUserHome base = (BaseUserHome)list.get(0);
			
			Object[] fields = new Object[2];
			
			fields[0] = theForm.getFcPicIDSts();			//	房产证照片状态
			fields[1] = theForm.getSeqID();				//	序号ID
			
			sql = servSql.getUpdstsSql();
			
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 更新记录失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			
			//验证通过，会员积分 增加900
			if(theForm.getFcPicIDSts().equals(GlbFinal.CHKFLAG_PASS)){
				
				JifenServiceImpl jifenservice = new JifenServiceImpl();			
				jifenservice.compjf(cn, 2, base.getUserID(), msg);
				if(msg.getErrCode() != 0){
					return;
				}				
			}
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);
			
		} catch(Exception ex) 
		{
			logger.error("System error , param Update about userdetails" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}	
		} finally {
			DbcpPool.returnConnection(cn);
		}
				
		return ;
	}
	/**
	 * 更新一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void update(UserHomeForm theForm, ReturnMessage msg) 
	{
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			DbcpPool.beginTran(cn);

			//取原记录的信息			
			Object[] fields2 = new Object[1];

			fields2[0] = theForm.getUserID();
			
			List list = null;
			
			UserHomeServiceSqlMysql servSql = new UserHomeServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			
			String sql = servSql.getByUserIDSql();
		
			list = bSqlServ.dbRead(cn, sql, fields2, new DbUserHomeService());

			//记录不存在 新增记录
			if(list.size() == 0){
				
				add(cn, theForm, msg);
				if(msg.getErrCode() != 0){
					return;
				}
				
				DbcpPool.commitTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;				
			}
			
			//填充到theForm的空字段中
			BaseUserHome base = (BaseUserHome)list.get(0);
			
			//房产证照片
			if(theForm.getFcPicIDPath() != null && !theForm.getFcPicIDPath().getFileName().equals("")){
				
				FilesServiceImpl fileservice = new FilesServiceImpl();
				FilesForm fileForm = new FilesForm();
				
				fileForm.setFormFile(theForm.getFcPicIDPath());
				fileForm.setF_info(theForm.getFangchanNo());
				fileForm.setF_Zip(GlbFinal.ZIP_200_200);
				
				if(base.getFcPicID() > 0){
					fileForm.setF_ID(base.getFcPicID());
					fileservice.update(cn, fileForm, msg);
				}else{
					
					fileservice.add(cn, fileForm, msg);
				}
				if(msg.getErrCode() != 0){
					return;
				}
				theForm.setFcPicID(fileForm.getF_ID());
			}						
			//取得FORM中传入的查询参数
			Object[] fields = new Object[19];
			
			fields[0] = theForm.getUserID();			//	会员ID
			fields[1] = theForm.getWork_location();		//	所在省份
			fields[2] = theForm.getWork_sublocation();	//	所在城市
			fields[3] = theForm.getWork_area();			//	所在区县
			fields[4] = theForm.getVillageID();			//	小区
			fields[5] = theForm.getBuilding();			//	栋
			fields[6] = theForm.getHouseNo();			//	号
			fields[7] = theForm.getFangchanNo();			//	房产证号码
			fields[8] = theForm.getFcNoSet();			//	房产证号码隐私保护
			fields[9] = theForm.getFcPicID();			//	房产证照片ID
			fields[10] = theForm.getFcPicIDSts();			//	房产证照片状态
			fields[11] = theForm.getInTime();			//	入住时间
			fields[12] = theForm.getInTimeSet();			//	入住时间隐私保护
			fields[13] = theForm.getFamSts();			//	家庭状况
			fields[14] = theForm.getFamStsSet();			//	家庭状况隐私保护
			fields[15] = theForm.getLiveSts();			//	居住状况
			fields[16] = theForm.getLiveStsSet();		//	居住状况隐私保护
			fields[17] = DatetimeUtil.getDatetime();
			fields[18] = base.getSeqID();				//	序号ID
			
			sql = servSql.getUpdateSql();
			
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 更新记录失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);
			
		} catch(Exception ex) 
		{
			logger.error("System error , param Update about userdetails" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}	
		} finally {
			DbcpPool.returnConnection(cn);
		}
				
		return ;
	}
	
	


}
